/**
 * Copyright 2012 Simple-Jdbc-Lightweight

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 */
package org.mlu.jdbc.simple.utils;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Map;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Mlungisi Sincuba
 * @since 1.0
 */
public abstract class JdbcUtils {

    public static final int TYPE_UNKNOWN = -2147483648;
    private static final Logger logger = LoggerFactory.getLogger(JdbcUtils.class);
    public static final String SELECT = "select * from ";
    public static final String UPDATE = "update ";
    public static final String DELETE = "delete from ";
    public static final String WHERE = " where ";
    public static final String AND = " and ";
    public static final String OR = " or ";
    public static final String EQUAL = " = ";
    public static final String ORDERBY = " order by ";
    public static final String ASC = " asc";
    public static final String DESC = " desc";

    public static void closeDbRecources(Connection con, Statement statement) {
        closeConnection(con);
        closeStatement(statement);
    }

    public static void closeDbRecources(Connection con, Statement statement, ResultSet resultSet) {
        closeDbRecources(con, statement);
        closeResultSet(resultSet);
    }

    public static void closeDbResources(Connection con, ResultSet resultSet) {
        closeResultSet(resultSet);
        closeConnection(con);
    }

    public static void closeConnection(Connection con) {
        if (con != null) {
            try {
                con.close();
            } catch (SQLException ex) {
                logger.debug("Could not close JDBC Connection", ex);
            } catch (Throwable ex) {
                logger.debug("Unexpected exception on closing JDBC Connection", ex);
            }
        }
    }

    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException ex) {
                logger.trace("Could not close JDBC Statement", ex);
            } catch (Throwable ex) {
                logger.trace("Unexpected exception on closing JDBC Statement", ex);
            }
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.trace("Could not close JDBC ResultSet", ex);
            } catch (Throwable ex) {
                logger.trace("Unexpected exception on closing JDBC ResultSet", ex);
            }
        }
    }

    public static Object getResultSetValue(ResultSet rs, int index, Class requiredType)
            throws SQLException {
        if (requiredType == null) {
            return getResultSetValue(rs, index);
        }

        Object value = null;
        boolean wasNullCheck = false;

        if (String.class.equals(requiredType)) {
            value = rs.getString(index);
        } else if ((Boolean.TYPE.equals(requiredType)) || (Boolean.class.equals(requiredType))) {
            value = Boolean.valueOf(rs.getBoolean(index));
            wasNullCheck = true;
        } else if ((Byte.TYPE.equals(requiredType)) || (Byte.class.equals(requiredType))) {
            value = Byte.valueOf(rs.getByte(index));
            wasNullCheck = true;
        } else if ((Short.TYPE.equals(requiredType)) || (Short.class.equals(requiredType))) {
            value = Short.valueOf(rs.getShort(index));
            wasNullCheck = true;
        } else if ((Integer.TYPE.equals(requiredType)) || (Integer.class.equals(requiredType))) {
            value = Integer.valueOf(rs.getInt(index));
            wasNullCheck = true;
        } else if ((Long.TYPE.equals(requiredType)) || (Long.class.equals(requiredType))) {
            value = Long.valueOf(rs.getLong(index));
            wasNullCheck = true;
        } else if ((Float.TYPE.equals(requiredType)) || (Float.class.equals(requiredType))) {
            value = Float.valueOf(rs.getFloat(index));
            wasNullCheck = true;
        } else if ((Double.TYPE.equals(requiredType)) || (Double.class.equals(requiredType))
                || (Number.class.equals(requiredType))) {
            value = Double.valueOf(rs.getDouble(index));
            wasNullCheck = true;
        } else if (java.sql.Date.class.equals(requiredType)) {
            value = rs.getDate(index);
        } else if (Time.class.equals(requiredType)) {
            value = rs.getTime(index);
        } else if ((Timestamp.class.equals(requiredType)) || (java.util.Date.class.equals(requiredType))) {
            value = rs.getTimestamp(index);
        } else if (BigDecimal.class.equals(requiredType)) {
            value = rs.getBigDecimal(index);
        } else if (Blob.class.equals(requiredType)) {
            value = rs.getBlob(index);
        } else if (Clob.class.equals(requiredType)) {
            value = rs.getClob(index);
        } else {
            value = getResultSetValue(rs, index);
        }

        if ((wasNullCheck) && (value != null) && (rs.wasNull())) {
            value = null;
        }
        return value;
    }

    public static Object getResultSetValue(ResultSet rs, int index)
            throws SQLException {
        Object obj = rs.getObject(index);
        String className = null;
        if (obj != null) {
            className = obj.getClass().getName();
        }
        if ((obj instanceof Blob)) {
            obj = rs.getBytes(index);
        } else if ((obj instanceof Clob)) {
            obj = rs.getString(index);
        } else if ((className != null) && (("oracle.sql.TIMESTAMP".equals(className))
                || ("oracle.sql.TIMESTAMPTZ".equals(className)))) {
            obj = rs.getTimestamp(index);
        } else if ((className != null) && (className.startsWith("oracle.sql.DATE"))) {
            String metaDataClassName = rs.getMetaData().getColumnClassName(index);
            if (("java.sql.Timestamp".equals(metaDataClassName))
                    || ("oracle.sql.TIMESTAMP".equals(metaDataClassName))) {
                obj = rs.getTimestamp(index);
            } else {
                obj = rs.getDate(index);
            }
        } else if ((obj != null) && ((obj instanceof java.sql.Date))
                && ("java.sql.Timestamp".equals(rs.getMetaData().getColumnClassName(index)))) {
            obj = rs.getTimestamp(index);
        } else if ((className != null) && (className.startsWith("java.sql.Timestamp"))) {
            obj = rs.getTimestamp(index);
        }

        return obj;
    }

    public static Object extractDatabaseMetaData(DataSource dataSource)
            throws SQLException {
        Connection con = null;
        try {
            con = dataSource.getConnection();
            if (con == null) {
                throw new IllegalArgumentException("Connection returned by DataSource [" + dataSource + "] was null");
            }
            DatabaseMetaData metaData = con.getMetaData();
            if (metaData == null) {
                throw new IllegalArgumentException("DatabaseMetaData returned by Connection [" + con + "] was null");
            }
            return metaData;
        } catch (SQLException ex) {
            throw new IllegalArgumentException("Error while extracting DatabaseMetaData", ex);
        } catch (AbstractMethodError err) {
            throw new IllegalArgumentException(
                    "JDBC DatabaseMetaData method not implemented by JDBC driver - upgrade your driver", err);
        } finally {
            closeConnection(con);
        }
    }

    public static boolean supportsBatchUpdates(Connection con) {
        try {
            DatabaseMetaData dbmd = con.getMetaData();
            if (dbmd != null) {
                if (dbmd.supportsBatchUpdates()) {
                    logger.debug("JDBC driver supports batch updates");
                    return true;
                }

                logger.debug("JDBC driver does not support batch updates");
            }
        } catch (SQLException ex) {
            logger.debug("JDBC driver 'supportsBatchUpdates' method threw exception", ex);
        } catch (AbstractMethodError err) {
            logger.debug("JDBC driver does not support JDBC 2.0 'supportsBatchUpdates' method", err);
        }
        return false;
    }

    public static String commonDatabaseName(String source) {
        String name = source;
        if ((source != null) && (source.startsWith("DB2"))) {
            name = "DB2";
        } else if (("Sybase SQL Server".equals(source))
                || ("Adaptive Server Enterprise".equals(source))
                || ("ASE".equals(source))
                || ("sql server".equalsIgnoreCase(source))) {
            name = "Sybase";
        }
        return name;
    }

    public static boolean isNumeric(int sqlType) {
        return (-7 == sqlType) || (-5 == sqlType) || (3 == sqlType)
                || (8 == sqlType) || (6 == sqlType) || (4 == sqlType)
                || (2 == sqlType) || (7 == sqlType) || (5 == sqlType)
                || (-6 == sqlType);
    }

    public static String lookupColumnName(ResultSetMetaData resultSetMetaData, int columnIndex)
            throws SQLException {
        String name = resultSetMetaData.getColumnLabel(columnIndex);
        if ((name == null) || (name.length() < 1)) {
            name = resultSetMetaData.getColumnName(columnIndex);
        }
        return name;
    }

    public static String convertUnderscoreNameToPropertyName(String name) {
        StringBuilder result = new StringBuilder();
        boolean nextIsUpper = false;
        if ((name != null) && (name.length() > 0)) {
            if ((name.length() > 1) && (name.substring(1, 2).equals("_"))) {
                result.append(name.substring(0, 1).toUpperCase());
            } else {
                result.append(name.substring(0, 1).toLowerCase());
            }
            for (int i = 1; i < name.length(); i++) {
                String s = name.substring(i, i + 1);
                if (s.equals("_")) {
                    nextIsUpper = true;
                } else if (nextIsUpper) {
                    result.append(s.toUpperCase());
                    nextIsUpper = false;
                } else {
                    result.append(s.toLowerCase());
                }
            }
        }

        return result.toString();
    }

    public static String convertTableNameToClassName(String name) {
        String className = convertUnderscoreNameToPropertyName(name);
        className = className.substring(0, 1).toUpperCase() + className.substring(1);
        return className;
    }
    
    public static String convertPropertyNameToUnderscoreName(String name) {
        StringBuilder result = new StringBuilder();
        if ((name != null) && (name.length() > 0)) {
            for (int i = 0; i < name.length(); i++) {
                char c = name.charAt(i);
                if (Character.isUpperCase(c)) {
                    result.append("_");
                    String s = String.valueOf(c);
                    result.append(s.toLowerCase());
                } else {
                    result.append(c);
                }
            }
        }
        return result.toString();
    }

    public static PreparedStatement getPreparedStatement(Connection connection, String sql, Object... params) throws SQLException {
        if (connection == null) {
            throw new IllegalArgumentException("Connection cannot be null");
        }
        PreparedStatement ps = connection.prepareStatement(sql);
        if (params == null) {
            return ps;
        }
        int i = 1;
        for (Object o : params) {
            ps.setObject(i, o);
            i++;
        }
        return ps;
    }

    public static PreparedStatement getPreparedStatement(Connection connection, String sql) throws SQLException {
        if (connection == null) {
            throw new IllegalArgumentException("Connection cannot be null");
        }
        PreparedStatement ps = connection.prepareStatement(sql);

        return ps;
    }

    public static Connection getConnection(DataSource dataSource) throws SQLException {
        if (dataSource == null) {
            throw new IllegalArgumentException("DataSource cannot be null");
        }
        return dataSource.getConnection();
    }

    public static String buildQuery(String tableName, String queryType, String[] params, String[] values, String orderByField, String ascDesc) {
        String query = "";
        StringBuilder st = new StringBuilder();
        if (queryType.equals(DELETE)) {
            query = query.concat(DELETE) + tableName + WHERE;
        }
        if (queryType.equals(SELECT)) {
            query = query.concat(SELECT) + tableName + WHERE;
        }
        int paramLength = params.length;
        for (int i = 0; i < paramLength; i++) {
            if (i > 0) {
                st.append(" and ").append(params[i]).append("='").append(values[i]).append("'");
            } else {
                st.append(params[i]).append("='").append(values[i]).append("'");
            }
        }
        if (orderByField != null) {
            orderByField = orderByField.concat(" ");
            st.append(ORDERBY).append(orderByField).append(ascDesc);
        }
        query = query + st.toString();
        return query;
    }
    
    public static String convertClassNameToTableName(String name) {
        String className = convertPropertyNameToUnderscoreName(name);
        className = className.substring(1);
        return className;
    }
    
    public static String getPrimaryKey(String tableName, DataSource dataSource) throws SQLException {
        Map<String, Boolean> primaryKeysForTable = SchemaUtil.getPrimaryKeysForTable(dataSource.getConnection(), tableName);
        for (Map.Entry<String, Boolean> entry : primaryKeysForTable.entrySet()) {
            String key = entry.getKey();
            return key;
        }
        return "";
    }
}
